package com.kexio.common.excel;

import com.kexio.common.excel.annotation.ExcelField;
import com.kexio.common.excel.annotation.ExcelSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel导入导出工具类
 * 基于Apache POI实现
 * 
 * @author Kexio
 */
public class ExcelUtils {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    
    /**
     * 导出Excel到Response
     */
    public static <T> void exportExcel(List<T> dataList, Class<T> clazz, 
                                       String fileName, HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
        
        // 导出到流
        try (OutputStream out = response.getOutputStream()) {
            exportExcel(dataList, clazz, out);
            out.flush();
        }
    }
    
    /**
     * 导出Excel到输出流
     */
    public static <T> void exportExcel(List<T> dataList, Class<T> clazz, OutputStream out) throws IOException {
        XSSFWorkbook workbook = createWorkbook(dataList, clazz);
        workbook.write(out);
        workbook.close();
    }
    
    /**
     * 创建Excel工作簿
     */
    public static <T> XSSFWorkbook createWorkbook(List<T> dataList, Class<T> clazz) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        // 获取Sheet配置
        ExcelSheet sheetAnnotation = clazz.getAnnotation(ExcelSheet.class);
        String sheetName = sheetAnnotation != null ? sheetAnnotation.name() : "Sheet1";
        XSSFSheet sheet = workbook.createSheet(sheetName);
        
        // 获取所有带@ExcelField注解的字段
        List<FieldWrapper> fields = getExcelFields(clazz);
        if (fields.isEmpty()) {
            logger.warn("类 {} 没有标注@ExcelField注解的字段", clazz.getName());
            return workbook;
        }
        
        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook, sheetAnnotation);
        CellStyle dataStyle = createDataStyle(workbook);
        
        // 创建表头
        Row headerRow = sheet.createRow(0);
        if (sheetAnnotation != null) {
            headerRow.setHeightInPoints(sheetAnnotation.headerHeight());
        }
        
        for (int i = 0; i < fields.size(); i++) {
            FieldWrapper fieldWrapper = fields.get(i);
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(fieldWrapper.getTitle());
            cell.setCellStyle(headerStyle);
            
            // 设置列宽
            sheet.setColumnWidth(i, fieldWrapper.getWidth() * 256);
        }
        
        // 填充数据
        if (dataList != null && !dataList.isEmpty()) {
            int rowIndex = 1;
            for (T data : dataList) {
                Row dataRow = sheet.createRow(rowIndex++);
                if (sheetAnnotation != null) {
                    dataRow.setHeightInPoints(sheetAnnotation.rowHeight());
                }
                
                for (int i = 0; i < fields.size(); i++) {
                    FieldWrapper fieldWrapper = fields.get(i);
                    Cell cell = dataRow.createCell(i);
                    cell.setCellStyle(dataStyle);
                    
                    try {
                        Object value = fieldWrapper.getField().get(data);
                        setCellValue(cell, value, fieldWrapper);
                    } catch (IllegalAccessException e) {
                        logger.error("获取字段值失败: {}", fieldWrapper.getField().getName(), e);
                    }
                }
            }
        }
        
        // 冻结窗格
        if (sheetAnnotation != null && sheetAnnotation.freezeRows() > 0) {
            sheet.createFreezePane(0, sheetAnnotation.freezeRows());
        }
        
        // 自动调整列宽
        if (sheetAnnotation != null && sheetAnnotation.autoSizeColumn()) {
            for (int i = 0; i < fields.size(); i++) {
                sheet.autoSizeColumn(i);
            }
        }
        
        return workbook;
    }
    
    /**
     * 从Excel导入数据
     */
    public static <T> List<T> importExcel(InputStream inputStream, Class<T> clazz) throws IOException {
        List<T> resultList = new ArrayList<>();
        
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取Sheet配置
            ExcelSheet sheetAnnotation = clazz.getAnnotation(ExcelSheet.class);
            int dataStartRow = sheetAnnotation != null ? sheetAnnotation.dataStartRow() : 1;
            
            // 获取所有带@ExcelField注解的字段
            List<FieldWrapper> fields = getExcelFields(clazz);
            if (fields.isEmpty()) {
                logger.warn("类 {} 没有标注@ExcelField注解的字段", clazz.getName());
                return resultList;
            }
            
            // 读取数据行
            for (int rowIndex = dataStartRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                
                try {
                    T instance = clazz.getDeclaredConstructor().newInstance();
                    boolean hasData = false;
                    
                    for (int i = 0; i < fields.size(); i++) {
                        FieldWrapper fieldWrapper = fields.get(i);
                        if (!fieldWrapper.isImportField()) {
                            continue;
                        }
                        
                        Cell cell = row.getCell(i);
                        Object value = getCellValue(cell, fieldWrapper);
                        
                        if (value != null) {
                            fieldWrapper.getField().set(instance, value);
                            hasData = true;
                        }
                    }
                    
                    if (hasData) {
                        resultList.add(instance);
                    }
                } catch (Exception e) {
                    logger.error("导入第{}行数据失败", rowIndex + 1, e);
                }
            }
        }
        
        return resultList;
    }
    
    /**
     * 获取Excel字段列表
     */
    private static <T> List<FieldWrapper> getExcelFields(Class<T> clazz) {
        List<FieldWrapper> fieldList = new ArrayList<>();
        
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelField annotation = field.getAnnotation(ExcelField.class);
            if (annotation != null) {
                field.setAccessible(true);
                fieldList.add(new FieldWrapper(field, annotation));
            }
        }
        
        // 按index排序
        fieldList.sort(Comparator.comparingInt(FieldWrapper::getIndex));
        
        return fieldList;
    }
    
    /**
     * 设置单元格值
     */
    private static void setCellValue(Cell cell, Object value, FieldWrapper fieldWrapper) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }
        
        ExcelField annotation = fieldWrapper.getAnnotation();
        
        // 应用导出转换表达式
        if (!annotation.exportConverterExp().isEmpty()) {
            value = convertByExp(value.toString(), annotation.exportConverterExp(), false);
        }
        
        // 根据类型设置值
        if (value instanceof String) {
            cell.setCellValue((String) value + annotation.suffix());
        } else if (value instanceof Number) {
            double numValue = ((Number) value).doubleValue();
            if (annotation.scale() >= 0) {
                BigDecimal bd = BigDecimal.valueOf(numValue);
                bd = bd.setScale(annotation.scale(), 
                    annotation.roundingMode() ? RoundingMode.HALF_UP : RoundingMode.DOWN);
                cell.setCellValue(bd.doubleValue());
            } else {
                cell.setCellValue(numValue);
            }
            if (!annotation.suffix().isEmpty()) {
                cell.setCellValue(cell.getNumericCellValue() + annotation.suffix());
            }
        } else if (value instanceof Date) {
            String dateStr = new java.text.SimpleDateFormat(annotation.dateFormat()).format((Date) value);
            cell.setCellValue(dateStr);
        } else if (value instanceof LocalDateTime) {
            String dateStr = ((LocalDateTime) value).format(DateTimeFormatter.ofPattern(annotation.dateFormat()));
            cell.setCellValue(dateStr);
        } else if (value instanceof LocalDate) {
            String dateStr = ((LocalDate) value).format(DateTimeFormatter.ofPattern(annotation.dateFormat()));
            cell.setCellValue(dateStr);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value ? "是" : "否");
        } else {
            cell.setCellValue(value.toString());
        }
    }
    
    /**
     * 获取单元格值
     */
    private static Object getCellValue(Cell cell, FieldWrapper fieldWrapper) {
        if (cell == null) {
            return null;
        }
        
        ExcelField annotation = fieldWrapper.getAnnotation();
        String cellValue = "";
        
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = new java.text.SimpleDateFormat(annotation.dateFormat()).format(cell.getDateCellValue());
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        
        // 应用读取转换表达式
        if (!annotation.readConverterExp().isEmpty()) {
            cellValue = convertByExp(cellValue, annotation.readConverterExp(), true);
        }
        
        // 转换为目标类型
        return convertToFieldType(cellValue, fieldWrapper.getField().getType(), annotation);
    }
    
    /**
     * 转换表达式
     */
    private static String convertByExp(String value, String exp, boolean isRead) {
        String[] exps = exp.split(",");
        for (String item : exps) {
            String[] pair = item.split("=");
            if (pair.length == 2) {
                if (isRead && pair[0].equals(value)) {
                    return pair[1];
                } else if (!isRead && pair[1].equals(value)) {
                    return pair[0];
                }
            }
        }
        return value;
    }
    
    /**
     * 转换为字段类型
     */
    private static Object convertToFieldType(String value, Class<?> fieldType, ExcelField annotation) {
        if (value == null || value.trim().isEmpty()) {
            return null;
        }
        
        try {
            if (fieldType == String.class) {
                return value;
            } else if (fieldType == Integer.class || fieldType == int.class) {
                return Integer.parseInt(value.contains(".") ? value.split("\\.")[0] : value);
            } else if (fieldType == Long.class || fieldType == long.class) {
                return Long.parseLong(value.contains(".") ? value.split("\\.")[0] : value);
            } else if (fieldType == Double.class || fieldType == double.class) {
                return Double.parseDouble(value);
            } else if (fieldType == Float.class || fieldType == float.class) {
                return Float.parseFloat(value);
            } else if (fieldType == Boolean.class || fieldType == boolean.class) {
                return "是".equals(value) || "true".equalsIgnoreCase(value) || "1".equals(value);
            } else if (fieldType == BigDecimal.class) {
                return new BigDecimal(value);
            } else if (fieldType == Date.class) {
                return new java.text.SimpleDateFormat(annotation.dateFormat()).parse(value);
            } else if (fieldType == LocalDateTime.class) {
                return LocalDateTime.parse(value, DateTimeFormatter.ofPattern(annotation.dateFormat()));
            } else if (fieldType == LocalDate.class) {
                return LocalDate.parse(value, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            }
        } catch (Exception e) {
            logger.error("类型转换失败: value={}, targetType={}", value, fieldType.getName(), e);
        }
        
        return null;
    }
    
    /**
     * 创建表头样式
     */
    private static CellStyle createHeaderStyle(XSSFWorkbook workbook, ExcelSheet sheetAnnotation) {
        CellStyle style = workbook.createCellStyle();
        
        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        // 字体
        XSSFFont font = workbook.createFont();
        if (sheetAnnotation != null && sheetAnnotation.headerBold()) {
            font.setBold(true);
        }
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);
        
        // 背景色
        if (sheetAnnotation != null) {
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        
        return style;
    }
    
    /**
     * 创建数据样式
     */
    private static CellStyle createDataStyle(XSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        
        // 边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        // 对齐方式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        return style;
    }
    
    /**
     * 字段包装类
     */
    private static class FieldWrapper {
        private final Field field;
        private final ExcelField annotation;
        
        public FieldWrapper(Field field, ExcelField annotation) {
            this.field = field;
            this.annotation = annotation;
        }
        
        public Field getField() {
            return field;
        }
        
        public ExcelField getAnnotation() {
            return annotation;
        }
        
        public String getTitle() {
            if (!annotation.name().isEmpty()) {
                return annotation.name();
            }
            if (!annotation.value().isEmpty()) {
                return annotation.value();
            }
            return field.getName();
        }
        
        public int getIndex() {
            return annotation.index() >= 0 ? annotation.index() : Integer.MAX_VALUE;
        }
        
        public int getWidth() {
            return annotation.width();
        }
        
        public boolean isExport() {
            return annotation.export();
        }
        
        public boolean isImportField() {
            return annotation.importField();
        }
    }
}

